// Trade-Policy Dynamics: Evidence from 60 Years of U.S.-China Trade
// Alessandria,  Khan, Khederlarian, Ruhl, and Steinberg

// inputs:  nberces5818v1_s1987.dta 
//			bbg_fam_drop_50_s4_2.dta 
//          gaps_by_naics6_20150722_fam50.dta
//			files in Feenstra/
//			conc_sic72_sic87.dta
//			files in Schott/
//			uncodes_unique_wbcodes.dta
//			tradecoeffs.dta

// outputs: emp_sic1987.dta
//			emp_effects.csv

// Builds the dataset needed for the emloymnet analysis in the manuscript and
// in the appendix. Produces the output needed for figure 7. 

clear all 
set varabbrev off

timer on 1
global dir_raw "../01 Raw data/"
global dir_int "../20 Intermediate files/"


// Set to 1 to produce the output for the associated figure. 
loc f7 1

use "${dir_raw}nberces5818v1_s1987.dta"

////////////////////////////////////////////////////////////////////////////////
// Merge the NBER CES dataset with Pierce and Schott's (2016) controls and tariff gaps.
{
	rename sic s4
	merge m:1 s4 using "${dir_raw}bbg_fam_drop_50_s4_2.dta" 
	drop if _merge!=3
	drop _merge 

	rename s4 sic
	merge m:1 fam50 using "${dir_raw}/gaps_by_naics6_20150722_fam50.dta"
	drop if _merge!=3
	drop _merge 
	tostring sic, replace
}

////////////////////////////////////////////////////////////////////////////////
// Merge import and export data into the dataset. 
{
	// The 1958-1994 aggregate trade data are from Feenstra.
	frame create trade5894RF
	frame change trade5894RF
	import delimited "${dir_raw}Feenstra/sic58_94.asc", clear
	replace year = year+1900
	loc i = 0
	forval y = 1958/1971 { // assign shares according to NBER-CES n-to-n matching
		loc i = `i'+1
		preserve
		keep if year==`y'
		merge 1:m sic72 using "${dir_raw}conc_sic72_sic87.dta", keepusing(sic87 sh7287)
		rename importvaluecif vim_gt
		rename exportvalue vex_gt
		rename industryshipments vship2
		rename sic87 sic
		tostring sic, replace
		foreach x in vim_gt vex_gt vship2 {
			replace `x' = `x'*sh7287
		}
		collapse (sum) v*, by(sic year)
		if `i'>1 {
			append using "${dir_int}temp.dta"
		}
		save "${dir_int}temp.dta", replace
		restore
		sleep 2000
	}
	frame change default
	merge 1:1 sic year using "${dir_int}temp.dta"
	drop _merge
	erase "${dir_int}temp.dta"
	
	// The trade data for 1972-2005 are from Peter Schott. 
	frame create trade
	frame change trade
	use "${dir_raw}Schott/xm_sic87_72_105_20120424.dta", clear
	tostring sic, replace
	keep if sic!="" & sic!="." & real(sic)!=.
	collapse (sum) customs cif x, by(sic year)
	rename customs vim
	rename cif vimcif
	rename x vex
	foreach x in vim vimcif vex {
		rename `x' `x'_gt
	}
	
	// The trade data post-2006 are from Peter Schott

	frame create trade0618PS
	frame change trade0618PS
	loc i = 0
	forval y = 106/118 {
		loc i = `i'+1
		use "${dir_raw}Schott/imp_detl_yearly_`y'n.dta", clear
		collapse (sum) con_val_yr con_cha_yr, by(sic year)
		gen cif = con_val_yr + con_cha_yr
		drop con_cha_yr
		if `i'>1 {
			append using "${dir_int}temp.dta"
		}
		save "${dir_int}temp", replace
	}

	rename con_val_yr vim_gt
	rename cif vimcif_gt
	drop if real(sic)==. | real(sic)<2000 | real(sic)>4000
	save "${dir_int}temp", replace
	loc i = 0
	forval y = 106/118 {
		loc i = `i'+1
		use "${dir_raw}Schott/exp_detl_yearly_`y'n.dta", clear
		collapse (sum) all_val_yr, by(sic year)
		if `i'>1 {
			append using "${dir_int}temp2.dta"
		}
		save "${dir_int}temp2", replace
	}
	rename all_val_yr vex_gt
	drop if real(sic)==. | real(sic)<2000 | real(sic)>4000
	merge 1:1 sic year using "${dir_int}temp.dta"
	drop _merge
	foreach v in vim_gt vex_gt vimcif_gt {
		replace `v' = `v'/1e6
	}
	save "${dir_int}temp.dta", replace
	frame change trade
	append using "${dir_int}temp.dta"
	erase "${dir_int}temp.dta"
	erase "${dir_int}temp2.dta"

	save "${dir_int}temp.dta", replace

	frame change default
	merge 1:1 sic year using "${dir_int}temp.dta", update
	drop _merge
	erase "${dir_int}temp.dta"
}

////////////////////////////////////////////////////////////////////////////////
// Merge tariff data into the dataset
{

	// Add tariff data 1972-1994 from Feenstra with applied tariffs
	frame create tariffs
	frame change tariffs
	loc i = 0
	forval y = 72/88 {
		loc i = `i'+1
		use "${dir_raw}Feenstra/imp`y'/imp`y'.dta" , clear
		gen sic = substr(sic87,1,4)
		collapse (sum) cusvalue duty, by(sic ccode yr)
		rename yr year
		if `i'>1 {
			append using "${dir_int}temp.dta"
		}
		save "${dir_int}temp", replace
		sleep 100
	}
	forval y = 89/94 {
		loc i = `i'+1
		use "${dir_raw}Feenstra/imp`y'/imp`y'_con.dta" , clear
		rename cvalue cusvalue
		collapse (sum) cusvalue duty, by(sic ccode year)
		append using "${dir_int}temp.dta"
		save "${dir_int}temp", replace
		sleep 100
	}
	replace year = year+1900
	drop if real(sic)==. | real(sic)<2000 | real(sic)>4000
	rename ccode uncode
	levelsof sic, loc(lsic)
	merge m:1 uncode using "${dir_int}uncodes_unique_wbcodes.dta", keepusing(wbcode)
	drop if _merge==2
	drop _merge
	collapse (sum) cusvalue duty, by(wbcode year sic)
	rename cusvalue vim_jgt
	rename duty duties_jgt
	foreach v of varlist *_jgt {
		replace `v' = `v'/1e6
	}

	// Add tariff data 1972-2005 from Peter Schott 
	frame create tariff9505PS
	frame change tariff9505PS
	use "${dir_raw}Schott/xm_sic87_72_105_20120424.dta", clear
	drop if year<1995
	tostring sic, replace
	keep if sic!="" & sic!="." & real(sic)!=.
	collapse (sum) customs duties, by(sic wbcode year)
	rename customs vim_jgt
	rename duties duties_jgt
	drop if wbcode=="" | vim_jgt==0
	save "${dir_int}temp.dta", replace

	frame change tariffs
	append using "${dir_int}temp.dta"

	// Add trade data post-2006 from Peter Schott
	frame create tariffs0622PS
	frame change tariffs0622PS
	loc i = 0
	forval y = 106/118 {
		loc i = `i'+1
		use "${dir_raw}Schott/imp_detl_yearly_`y'n.dta", clear
		collapse (sum) con_val_yr cal_dut_yr, by(sic cty_code year)
		if `i'>1 {
			append using "${dir_int}temp.dta"
		}
		save "${dir_int}temp", replace
	}
	rename con_val_yr vim_jgt
	rename cal_dut_yr duties_jgt
	drop if real(sic)==. | real(sic)<2000 | real(sic)>4000
	foreach v of varlist *_jgt {
		replace `v' = `v'/1e6
	}
	rename cty_code uscode
	merge m:1 uscode using "${dir_raw}uncodes_uscodes_wbcodes.dta", keepusing(wbcode uscountry)
	keep if _merge==3
	drop _merge
	collapse (sum) *_jgt, by(sic wbcode year)
	save "${dir_int}temp.dta", replace
	frame change tariffs
	append using "${dir_int}temp.dta"
	erase "${dir_int}temp.dta"
	foreach v of varlist *duties* {
		replace `v' = . if year<1974
	}


	mvencode duties_jgt if vim_jgt!=. , mv(0) override // treat missing duties with nonmissing trade as zero duties
	gen tar_savg_jgt = duties_jgt/vim_jgt
	egen tarchn_gt = mean(tar_savg_jgt/(wbcode=="CHN")), by(sic year)
	egen tarntr_gt = mean(tar_savg_jgt/(wbcode=="JPN")), by(sic year) 
	egen temp1 = mean(tar_savg_jgt/(wbcode=="GBR")), by(sic year)
	egen temp2 = mean(tar_savg_jgt/(wbcode=="FRA")), by(sic year)
	replace tarntr_gt = temp1 if tarntr_gt==. 
	replace tarntr_gt = temp2 if tarntr_gt==. 
	egen vimchn_gt = mean(vim_jgt/(wbcode=="CHN")), by(sic year)
	collapse (mean) tar_savg_jgt (sum) *vim_jgt *duties_jgt (first) *_gt, by(sic year)
	rename *jgt *gt
	gen tar_wavg_gt = duties_gt/vim_gt
	gen vimchnsh_gt = vimchn_gt/vim_gt
	drop vim_gt duties_gt
	save "${dir_int}temp.dta", replace

	frame change default
	merge 1:1 sic year using "${dir_int}temp.dta"
	drop _merge
	erase "${dir_int}temp.dta"

	mvencode *im_gt *imchn*_gt *ex_gt, mv(0) override
}

////////////////////////////////////////////////////////////////////////////////
// Calculations
{
	gen capint_gt = cap/emp
	egen capint90_g = mean(capint_gt/(year==1990)), by(sic)
	gen ntr_gt = .
	forval x = 1989/2001 {
		replace ntr_gt = ntr`x' if year==`x'
	}
	replace ntr_gt = ntr2001 if year>2001
	replace ntr_gt = ntr1989 if year<1989
	replace ntr_gt = tarntr_gt if ntr_gt==.

	drop if real(sic)==.
	destring sic, replace
	xtset sic year

	egen vimchnsh7579_g = mean(vimchnsh_gt/(year>=1975 & year<=1979)), by(sic)

	egen vimchnsh81_g = mean(vimchnsh_gt/(year==1981)), by(sic)
	egen vimchnsh79_g = mean(vimchnsh_gt/(year==1979)), by(sic)
	gen vimchnshdiff7981_g = vimchnsh81_g-vimchnsh79_g

	egen vimchnsh88_g = mean(vimchnsh_gt/(year==1988)), by(sic)
	egen vimchnsh85_g = mean(vimchnsh_gt/(year==1985)), by(sic)
	gen vimchnshdiff8588_g = vimchnsh88_g-vimchnsh85_g
	gen vimchnshdiff7988_g = vimchnsh88_g-vimchnsh79_g

	egen vimchn07_g = mean(vimchn_gt/(year==2007)), by(sic)
	egen vimchn97_g = mean(vimchn_gt/(year==1997)), by(sic)
	gen lvimchndiff9707_g = ln(vimchn07_g/vimchn97_g)

	egen vimrat07_g = mean((vim_gt/(vship-vex_gt))/(year==2007)), by(sic)
	egen vimrat97_g = mean((vim_gt/(vship-vex_gt))/(year==1997)), by(sic)
	gen lvimratgr9707_g = ln(vimrat07_g/vimrat97_g)

	gen lvimrat_gt = ln(vim_gt/(vship-vex_gt))


	gen vimchnsh_ship_gt = vimchn_gt/(vship)

	gen abs_gt = vship - vex_gt + vim_gt
	replace abs_gt = . if abs_gt<0

	gen vex_ship_gt = vex_gt/vship
	replace vex_ship_gt = . if vex_ship_gt>1
	gen vim_abs_gt = vim_gt/abs_gt
	replace vim_abs_gt = . if vim_abs_gt>1

	gen trdbal_trade_gt = (vex_gt-vim_gt)/(vex_gt+vim_gt)
	gen trade_abs_gt = (vex_gt+vim_gt)/(abs_gt)
	gen trade_gt = (vex_gt+vim_gt)

	gen nprode = emp - prode
	gen nprodw = pay - prodw
	gen wrate_gt = pay/emp
	gen wrateprod_gt = prodw/prode
	gen wratenprod_gt = (pay-prodw)/(emp-prode)

	xtset sic year
	gen nii_gt = (invent-L.invent)
	gen nii_ship_gt = (invent-L.invent)/vship
	gen niirat_gt = invent/L.invent
	gen inv_ship_gt = invent/vship

	gen prod_gt = vship+nii_gt

	foreach y in emp prode nprode prodh prodw nprodw wrate_gt wrateprod_gt wratenprod_gt abs_gt vship vex_gt vim_gt cap pay matcost vadd vimchn_gt vimchnshdiff7988_g vimchnsh_ship_gt tfp5 invent vimchnsh_gt vex_ship_gt vim_abs_gt piship piinv pien pimat invest trdbal_trade_gt trade_abs_gt trade_gt niirat_gt inv_ship_gt prod_gt {
		gen l`y' = ln(`y')
	}
	gen lhours_abs_gt = ln(prodh/abs_gt)

	foreach v of varlist tar* {
		gen l`v' = ln(1+`v')
	}

	foreach y in emp prode nprode prodh prodw nprodw wrate_gt wrateprod_gt wratenprod_gt abs_gt vship vex_gt vim_gt cap pay matcost vadd vimchn_gt vimchnsh_ship_gt tfp5 invent vimchnsh_gt vex_ship_gt vim_abs_gt piship piinv pien pimat invest trdbal_trade_gt trade_abs_gt trade_gt {
		egen temp1 = mean(l`y'/(year>=1998 & year<=2000)), by(sic)
		egen temp2 = mean(l`y'/(year>=2012 & year<=2014)), by(sic)
		gen ldiff9913`y' = temp2-temp1
		drop temp*
	}

	gen vdomsh_gt = (vship - vex_gt)/(vship)
	replace vdomsh_gt = . if vdomsh_gt<0
	gen vimchnsh_abs_gt = vimchn_gt/abs_gt
	replace vimchnsh_abs_gt = . if vimchnsh_abs_gt>1

	egen vdomsh7579_g = mean(vdomsh_gt/(year>=1975 & year<=1979)), by(sic)
	egen vimchnsh7579_ship_g = mean(vimchnsh_ship_gt/(year>=1975 & year<=1979)), by(sic)
	egen vimchnsh7579_abs_g = mean(vimchnsh_abs_gt/(year>=1975 & year<=1979)), by(sic)
	egen vimsh7579_abs_g = mean(vim_abs_gt/(year>=1975 & year<=1979)), by(sic)

	egen vdomsh8283_g = mean(vdomsh_gt/(year>=1982 & year<=1983)), by(sic)
	egen vimchnsh8283_ship_g = mean(vimchnsh_ship_gt/(year>=1982 & year<=1983)), by(sic)
	egen vimchnsh8283_abs_g = mean(vimchnsh_abs_gt/(year>=1982 & year<=1983)), by(sic)
	egen vimsh8283_abs_g = mean(vim_abs_gt/(year>=1982 & year<=1983)), by(sic)

	egen vdomsh9195_g = mean(vdomsh_gt/(year>=1991 & year<=1995)), by(sic)
	egen vimchnsh9195_ship_g = mean(vimchnsh_ship_gt/(year>=1991 & year<=1995)), by(sic)
	egen vimchnsh9195_abs_g = mean(vimchnsh_abs_gt/(year>=1991 & year<=1995)), by(sic)
	egen vimsh9195_abs_g = mean(vim_abs_gt/(year>=1991 & year<=1995)), by(sic)

	egen vdomsh9599_g = mean(vdomsh_gt/(year>=1995 & year<=1999)), by(sic)
	egen vimchnsh9599_ship_g = mean(vimchnsh_ship_gt/(year>=1995 & year<=1999)), by(sic)
	egen vimchnsh9599_abs_g = mean(vimchnsh_abs_gt/(year>=1995 & year<=1999)), by(sic)
	egen vimsh9599_abs_g = mean(vim_abs_gt/(year>=1995 & year<=1999)), by(sic)

	egen vdomsh0509_g = mean(vdomsh_gt/(year>=2005 & year<=2009)), by(sic)
	egen vimchnsh0509_ship_g = mean(vimchnsh_ship_gt/(year>=2005 & year<=2009)), by(sic)
	egen vimchnsh0509_abs_g = mean(vimchnsh_abs_gt/(year>=2005 & year<=2009)), by(sic)
	egen vimsh0509_abs_g = mean(vim_abs_gt/(year>=2005 & year<=2009)), by(sic)

	egen vdomsh90_g = mean(vdomsh_gt/(year==1990)), by(sic)
	egen vimchnsh90_ship_g = mean(vimchnsh_ship_gt/(year==1990)), by(sic)
	egen vimchnsh90_abs_g = mean(vimchnsh_abs_gt/(year==1990)), by(sic)
	egen vimsh90_abs_g = mean(vim_abs_gt/(year==1990)), by(sic)

	gen lvdomsh7579diff_gt = vdomsh_gt-vdomsh7579_g
	gen lvimchnsh7579diff_ship_gt = vimchnsh_ship_gt-vimchnsh7579_ship_g
	gen lvimchnsh7579diff_abs_gt = vimchnsh_abs_gt-vimchnsh7579_abs_g

	egen vdomshavg_g = mean(vdomsh_gt/(year>=1975)), by(sic)
	egen vimchnshavg_ship_g = mean(vimchnsh_ship_gt/(year>=1975)), by(sic)
	egen vimchnshavg_abs_g = mean(vimchnsh_abs_gt/(year>=1975)), by(sic)
	egen vimshavg_abs_g = mean(vim_abs_gt/(year>=1975)), by(sic)

	bysort sic: egen emp90_g = mean(emp/(year==1990))
	bysort sic: egen emp71_g = mean(emp/(year==1971))
	bysort sic: egen emp58_g = mean(emp/(year==1958))
	bysort sic: egen emp9599_g = mean(emp/(year>=1995&year<=1999))
	gen lempdiff_5871_g = ln(emp71_g/emp58_g)
	gen lempdiff_58_gt = ln(emp/emp58_g)

	xtset sic year
	forval l = 1/3 {
		foreach x in vimchnsh_ship_gt vimchnsh_gt vdomsh_gt vimchnsh_abs_gt {
			gen lag`l'`x' = L`l'.`x'
		}
	}

	foreach x of varlist lag* {
		egen pc75_`x' = pctile(`x'), p(75) by(year)
		gen Dhigh_`x' = `x'>=pc75_`x'
	}

	foreach v of varlist *_g s1999 {
		egen temp = mean(`v'), by(sic)
		replace `v' = temp if `v'==.
		drop temp*
	}

	egen tag_g =tag(sic)
	foreach x of varlist s1999 v*7579* v*9599* v*0509* vimchnshdiff7988_g {
		egen pc75_`x' = pctile(`x'/tag_g), p(75)
		egen pc25_`x' = pctile(`x'/tag_g), p(25)
		egen pc50_`x' = pctile(`x'/tag_g), p(50)
		egen pc33_`x' = pctile(`x'/tag_g), p(33)
		egen pc66_`x' = pctile(`x'/tag_g), p(66)
		gen Dhigh75_`x' = `x'>=pc75_`x'
		gen Dhigh50_`x' = `x'>=pc50_`x'
		gen quar`x' = 1 if `x'<pc25_`x'
		replace quar`x' = 2 if `x'>=pc25_`x' & `x'<pc50_`x'
		replace quar`x' = 3 if `x'>=pc50_`x' & `x'<pc75_`x'
		replace quar`x' = 4 if `x'>=pc75_`x'
		gen ter`x' = 1 if `x'<pc33_`x'
		replace ter`x' = 2 if `x'>=pc33_`x' & `x'<pc66_`x'
		replace ter`x' = 3 if `x'>=pc66_`x'
	}

	g sic3=substr(string(sic),1,3)
	encode sic3, g(sic3n)
	g sic2=substr(string(sic),1,2)
	encode sic2, g(sic2n)
	destring sic, replace
	egen tag_t =tag(year)
	g post = year>1999

	egen vim_balpan_g = count(year/(vim_gt!=0)), by(sic)
	egen vex_balpan_g = count(year/(vex_gt!=0)), by(sic)

	frame drop suppfac trade* adh13*

	egen vimchn_t = sum(vimchn_gt), by(year)
	egen vim_t = sum(vim_gt), by(year)
	gen vimchnsh_t = vimchn_t/vim_t
	
	// Add the trade coefficients and save dataset.
	merge m:1 year using "${dir_int}tradecoeffs.dta", nogen
	save "${dir_int}emp_sic1987.dta", replace
}

////////////////////////////////////////////////////////////////////////////////
// Figure 7
if `f7' == 1{
	use "${dir_int}emp_sic1987.dta", clear

	eststo clear
	gen tempabs = labs_gt - lpiship
	gen tempexp = lvex_gt - lpiship
	loc x1 vimchnsh9599_abs_g
	loc x2 vdomsh9599_g
	loc x3 s1999
	gen tempexpsh = 1 - `x2'
	gen tempy1 = lemp - c.`x2'#c.tempabs - c.tempexpsh#c.tempexp + ltfp5
	gen tempy2 = lemp - c.`x2'#c.tempabs - c.tempexpsh#c.tempexp
	loc wgt [aw=emp90_g]
	loc firstyr 1958
	loc lastyr 2008
	loc baseyr 2008
	loc chnsh 0.06
	loc domsh 0.91

	gen temptradecoeff = -`chnsh'*`domsh'*tradecoeff_base_t
	loc fe absorb(sic year)
	g tempbaseyr= year!=`baseyr'
	gen temp = year>=`firstyr' & year<=`lastyr'
	forval t = 1/4 {
		g temp_beta`t'=.
		g temp_beta`t'_se=.
	}
	eststo: reghdfe tempy1 ibn.year#i1.tempbaseyr#c.`x1'#c.`x2'#c.`x3'  `wgt' if temp, `fe' vce(cluster sic)
	forv t=`firstyr'/`lastyr' {
		qui replace temp_beta1=_b[`t'.year#i1.tempbaseyr#c.`x1'#c.`x2'#c.`x3']*`chnsh'*`domsh' if year==`t'
		qui replace temp_beta1_se=_se[`t'.year#i1.tempbaseyr#c.`x1'#c.`x2'#c.`x3']*`chnsh'*`domsh' if year==`t'
	}
	replace temp = temp & _est_est1==1
	eststo: qui reghdfe tempy2 ibn.year#i1.tempbaseyr#c.`x1'#c.`x2'#c.`x3' `wgt' if temp, `fe' vce(cluster sic)
	forv t=`firstyr'/`lastyr' {
		qui replace temp_beta2=_b[`t'.year#i1.tempbaseyr#c.`x1'#c.`x2'#c.`x3']*`chnsh'*`domsh' if year==`t'
		qui replace temp_beta2_se=_se[`t'.year#i1.tempbaseyr#c.`x1'#c.`x2'#c.`x3']*`chnsh'*`domsh' if year==`t'
	}
	replace temp = temp & _est_est2==1
	eststo: qui reghdfe lemp ibn.year#i1.tempbaseyr#c.`x1'#c.`x2'#c.`x3' `wgt' if temp, `fe' vce(cluster sic)
	forv t=`firstyr'/`lastyr' {
		qui replace temp_beta3=_b[`t'.year#i1.tempbaseyr#c.`x1'#c.`x2'#c.`x3']*`chnsh'*`domsh' if year==`t'
		qui replace temp_beta3_se=_se[`t'.year#i1.tempbaseyr#c.`x1'#c.`x2'#c.`x3']*`chnsh'*`domsh' if year==`t'
	}
	replace temp = temp & _est_est3==1
	eststo: qui reghdfe lemp ibn.year#i1.tempbaseyr#c.`x3' `wgt' if temp, `fe' vce(cluster sic)
	forv t=`firstyr'/`lastyr' {
		qui replace temp_beta4=_b[`t'.year#i1.tempbaseyr#c.`x3'] if year==`t'
		qui replace temp_beta4_se=_se[`t'.year#i1.tempbaseyr#c.`x3'] if year==`t'
	}
	loc ci 1.96
	forval t = 1/4 {
		g temp_beta`t'_ub = temp_beta`t'+`ci'*temp_beta`t'_se
		g temp_beta`t'_lb = temp_beta`t'-`ci'*temp_beta`t'_se
	}
	replace temp = tag_t & year>=`firstyr' & year<=`lastyr'
	sort year
	
	export delimited temp_beta1 temp_beta2 temp_beta3 temp_beta4 temp_beta1_ub temp_beta1_lb temptradecoeff year if temp using "${dir_int}emp-effects.csv", replace
}

timer off 1
timer list

